USE [Training]
GO

IF NOT EXISTS (SELECT * from dbo.sysobjects WHERE id = OBJECT_ID(N'training_api_DepartmentTreeSelectByLocation'))
	EXEC('CREATE PROC training_api_DepartmentTreeSelectByLocation AS')
GO

ALTER PROC [dbo].[training_api_DepartmentTreeSelectByLocation]
	@LocationID	int = null
AS
	SELECT
		dt.DepartmentTreeID,
		dt.LocationID,
		loc.Title [Location],
		p.DepartmentID [ParentId],
		p.Title [Parent],
		c.DepartmentID [ChildId],
		c.Title [Child]
	FROM 
		DepartmentTree dt JOIN
		Department p ON dt.Parent = p.DepartmentID JOIN
		Department c ON dt.Child = c.DepartmentID JOIN
		Location loc ON dt.LocationID = loc.LocationID		
	WHERE
		(@LocationID IS NULL OR(loc.LocationID = @LocationID))
